package com.example.application.service;

import com.example.application.bean.Info;
import com.example.application.bean.ShowInfo;
import com.example.application.config.Constant;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * author: fangjie email: syfangjie@live.cn date: 2021/1/3 12:09 下午
 */
public class SqlService {
  private static final Logger log = LoggerFactory.getLogger(SqlService.class);
  private Connection connect() {
    // SQLite connection string
    String url = "jdbc:sqlite:"+ Constant.DB_PATH;
    Connection conn = null;
    try {
      conn = DriverManager.getConnection(url);
    } catch (SQLException e) {
      System.out.println(e.getMessage());
    }
    return conn;
  }

  public void init(){
    try(Connection c=this.connect()){
      Statement stmt = c.createStatement();
      String sql ="CREATE TABLE IF NOT EXISTS Info(\n"
          + "   DEMAND_CHANNEL_GRP     CHAR(100),\n"
          + "   DEMAND_SEASON     CHAR(100),\n"
          + "   PLANT     CHAR(100),\n"
          + "   PRODUCT   CHAR(100),\n"
          + "   MDLDESC   CHAR(100),\n"
          + "   TASK_DATE_GR CHAR(100),\n"
          + "   RPTG_CNFRM_QTY CHAR(100),\n"
          + "   PLNGCTRYCD CHAR(100)\n"
          + ");";
      log.info(sql);
      stmt.executeUpdate(sql);
      stmt.close();
    } catch (SQLException throwables) {
      log.error(throwables.toString());
    }
  }

  public void save(List<Info> list)  {
    try(Connection c=this.connect()){
      log.info("Opened database successfully");
      String sql = "INSERT INTO Info(DEMAND_CHANNEL_GRP,DEMAND_SEASON,PLANT,PRODUCT,MDLDESC,TASK_DATE_GR,RPTG_CNFRM_QTY,PLNGCTRYCD) VALUES(?,?,?,?,?,?,?,?)";
      c.setAutoCommit(false);
      PreparedStatement pstmt = c.prepareStatement(sql);
      for (Info info:list){
        pstmt.setString(1,info.getDEMAND_CHANNEL_GRP());
        pstmt.setString(2,info.getDEMAND_SEASON());
        pstmt.setString(3,info.getPLANT());
        pstmt.setString(4,info.getPRODUCT());
        pstmt.setString(5,info.getMDLDESC());
        pstmt.setString(6,info.getTASK_DATE_GR());
        pstmt.setString(7,info.getRPTG_CNFRM_QTY());
        pstmt.setString(8,info.getPLNGCTRYCD());
        pstmt.addBatch();
      }
      pstmt.executeBatch();
      c.commit();
    } catch (SQLException throwables) {

    }
  }

  public List<Info> selectWhere(String DEMAND_SEASON, String DEMAND_CHANNEL_GRP, String MDLDESC, String PLNGCTRYCD){
    StringBuilder sb=new StringBuilder("SELECT * FROM Info Where");
    if(DEMAND_SEASON.trim().isEmpty()){
      sb.append(" 1=1 ");
    }
    else {
      String[] strs = DEMAND_SEASON.split(",");
      sb.append("(");
      for (int i = 0; i < strs.length; i++) {
        sb.append(" DEMAND_SEASON='").append(strs[i]).append("'");
        if(i!=strs.length-1)sb.append(" OR ");
      }
      sb.append(")");
    }
    if(! DEMAND_CHANNEL_GRP.trim().isEmpty() ){
      String[] strs = DEMAND_CHANNEL_GRP.split(",");
      sb.append(" AND (");
      for (int i = 0; i < strs.length; i++) {
        sb.append(" DEMAND_CHANNEL_GRP='").append(strs[i]).append("'");
        if(i!=strs.length-1)sb.append(" OR ");
      }
      sb.append(")");
    }
    if(! MDLDESC.trim().isEmpty() ){
      String[] strs = MDLDESC.split(",");
      sb.append(" AND (");
      for (int i = 0; i < strs.length; i++) {
        sb.append(" MDLDESC='").append(strs[i]).append("'");
        if(i!=strs.length-1)sb.append(" OR ");
      }
      sb.append(")");
    }
    if(! PLNGCTRYCD.trim().isEmpty() ){
      String[] strs = PLNGCTRYCD.split(",");
      sb.append(" AND (");
      for (int i = 0; i < strs.length; i++) {
        sb.append(" PLNGCTRYCD='").append(strs[i]).append("'");
        if(i!=strs.length-1)sb.append(" OR ");
      }
      sb.append(")");
    }
    sb.append(";");
    return select(sb.toString());
  }

  public List<Info> select(String sql) {
    log.info(sql);
    List<Info> res=new ArrayList<>();
    try ( Connection conn = this.connect()){

      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(sql);
      while (rs.next()){
        Info info=new Info();
        info.setDEMAND_CHANNEL_GRP(rs.getString("DEMAND_CHANNEL_GRP"));
        info.setDEMAND_SEASON(rs.getString("DEMAND_SEASON"));
        info.setPLANT(rs.getString("PLANT"));
        info.setPRODUCT(rs.getString("PRODUCT"));
        info.setMDLDESC(rs.getString("MDLDESC"));
        info.setTASK_DATE_GR(rs.getString("TASK_DATE_GR"));
        info.setRPTG_CNFRM_QTY(rs.getString("RPTG_CNFRM_QTY"));
        res.add(info);
      }
    } catch (SQLException e) {
      System.out.println(e.getMessage());
   }
    return res;
  }

  public static List<ShowInfo> InfoToShowInfo(List<Info> list){
    List<ShowInfo> res=new ArrayList<>(list.size());
    list.forEach(
        info -> {
          ShowInfo showInfo = new ShowInfo();
          showInfo.setPRODUCT(info.getPRODUCT());
          showInfo.setRPTG_CNFRM_QTY(info.getRPTG_CNFRM_QTY());
          showInfo.setTASK_DATE_GR(info.getTASK_DATE_GR());
          showInfo.setPLANT(info.getPLANT());
          res.add(showInfo);
        });

    return res;
  }

}
